using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Data;
using System.Text;

namespace Lzfy_Refund_Service.Repositories
{
    /// <summary>
    /// 用户修改日志仓储实现
    /// </summary>
    public class UumUserModifyLogRepository : IUumUserModifyLogRepository
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;
        private readonly ILogger<UumUserModifyLogRepository> _logger;

        public UumUserModifyLogRepository(IOracleConnectionFactory oracleConnectionFactory, ILogger<UumUserModifyLogRepository> logger)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 创建用户修改日志
        /// </summary>
        public async Task<long> CreateAsync(UumUserModifyLog log)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    INSERT INTO UUM.LZFY_UUM_USER_MODIFY_LOG 
                    (user_name, col_name, user_name_modify, origin_value, update_value)
                    VALUES 
                    (:UserName, :ColName, :UserNameModify, :OriginValue, :UpdateValue)
                    RETURNING id INTO :Id";

                var parameters = new DynamicParameters();
                parameters.Add("UserName", log.UserName);
                parameters.Add("ColName", log.ColName);
                parameters.Add("UserNameModify", log.UserNameModify);
                parameters.Add("OriginValue", log.OriginValue);
                parameters.Add("UpdateValue", log.UpdateValue);
                parameters.Add("Id", dbType: System.Data.DbType.Int64, direction: System.Data.ParameterDirection.Output);

                await connection.ExecuteAsync(sql, parameters);
                
                var id = parameters.Get<long>("Id");
                
                _logger.LogInformation("创建用户修改日志成功，ID: {Id}, 用户: {UserName}, 列名: {ColName}", 
                    id, log.UserName, log.ColName);
                
                return id;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "创建用户修改日志失败，用户: {UserName}, 列名: {ColName}", 
                    log.UserName, log.ColName);
                throw;
            }
        }

        /// <summary>
        /// 根据ID获取用户修改日志
        /// </summary>
        public async Task<UumUserModifyLog?> GetByIdAsync(long id)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT id, input_date, user_name, col_name, user_name_modify, origin_value, update_value
                    FROM UUM.LZFY_UUM_USER_MODIFY_LOG 
                    WHERE id = :Id";

                var result = await connection.QuerySingleOrDefaultAsync<UumUserModifyLog>(sql, new { Id = id });
                
                _logger.LogInformation("查询用户修改日志，ID: {Id}, 结果: {Found}", id, result != null ? "找到" : "未找到");
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询用户修改日志失败，ID: {Id}", id);
                throw;
            }
        }

        /// <summary>
        /// 分页查询用户修改日志
        /// </summary>
        public async Task<(IEnumerable<UumUserModifyLog> data, int totalCount)> GetPagedAsync(UumUserModifyLogQueryRequest request)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var whereClause = new StringBuilder();
                var parameters = new DynamicParameters();

                // 构建WHERE条件
                if (!string.IsNullOrEmpty(request.UserName))
                {
                    whereClause.Append(" AND user_name LIKE :UserName");
                    parameters.Add("UserName", $"%{request.UserName}%");
                }

                if (!string.IsNullOrEmpty(request.ColName))
                {
                    whereClause.Append(" AND col_name = :ColName");
                    parameters.Add("ColName", request.ColName);
                }

                if (!string.IsNullOrEmpty(request.UserNameModify))
                {
                    whereClause.Append(" AND user_name_modify LIKE :UserNameModify");
                    parameters.Add("UserNameModify", $"%{request.UserNameModify}%");
                }

                if (request.StartDate.HasValue)
                {
                    whereClause.Append(" AND input_date >= :StartDate");
                    parameters.Add("StartDate", request.StartDate.Value);
                }

                if (request.EndDate.HasValue)
                {
                    whereClause.Append(" AND input_date <= :EndDate");
                    parameters.Add("EndDate", request.EndDate.Value.AddDays(1).AddSeconds(-1));
                }

                var whereCondition = whereClause.Length > 0 ? $"WHERE 1=1 {whereClause}" : "";

                // 查询总数
                var countSql = $@"
                    SELECT COUNT(*) 
                    FROM UUM.LZFY_UUM_USER_MODIFY_LOG 
                    {whereCondition}";

                var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

                // 分页查询数据
                var offset = (request.PageIndex - 1) * request.PageSize;
                parameters.Add("Offset", offset);
                parameters.Add("PageSize", request.PageSize);

                var dataSql = $@"
                    SELECT * FROM (
                        SELECT id, input_date, user_name, col_name, user_name_modify, origin_value, update_value,
                               ROW_NUMBER() OVER (ORDER BY input_date DESC) as rn
                        FROM UUM.LZFY_UUM_USER_MODIFY_LOG 
                        {whereCondition}
                    ) WHERE rn > :Offset AND rn <= :Offset + :PageSize";

                var data = await connection.QueryAsync<UumUserModifyLog>(dataSql, parameters);

                _logger.LogInformation("分页查询用户修改日志，页码: {PageIndex}, 每页: {PageSize}, 总数: {TotalCount}", 
                    request.PageIndex, request.PageSize, totalCount);

                return (data, totalCount);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "分页查询用户修改日志失败");
                throw;
            }
        }

        /// <summary>
        /// 根据用户名获取修改日志
        /// </summary>
        public async Task<IEnumerable<UumUserModifyLog>> GetByUserNameAsync(string userName)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT id, input_date, user_name, col_name, user_name_modify, origin_value, update_value
                    FROM UUM.LZFY_UUM_USER_MODIFY_LOG 
                    WHERE user_name = :UserName
                    ORDER BY input_date DESC";

                var result = await connection.QueryAsync<UumUserModifyLog>(sql, new { UserName = userName });
                
                _logger.LogInformation("根据用户名查询修改日志，用户: {UserName}, 记录数: {Count}", 
                    userName, result.Count());
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据用户名查询修改日志失败，用户: {UserName}", userName);
                throw;
            }
        }

        /// <summary>
        /// 根据修改用户名获取修改日志
        /// </summary>
        public async Task<IEnumerable<UumUserModifyLog>> GetByUserNameModifyAsync(string userNameModify)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT id, input_date, user_name, col_name, user_name_modify, origin_value, update_value
                    FROM UUM.LZFY_UUM_USER_MODIFY_LOG 
                    WHERE user_name_modify = :UserNameModify
                    ORDER BY input_date DESC";

                var result = await connection.QueryAsync<UumUserModifyLog>(sql, new { UserNameModify = userNameModify });
                
                _logger.LogInformation("根据修改用户名查询修改日志，修改用户: {UserNameModify}, 记录数: {Count}", 
                    userNameModify, result.Count());
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据修改用户名查询修改日志失败，修改用户: {UserNameModify}", userNameModify);
                throw;
            }
        }

        /// <summary>
        /// 获取最近的修改日志
        /// </summary>
        public async Task<IEnumerable<UumUserModifyLog>> GetRecentAsync(int minutes = 60)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT id, input_date, user_name, col_name, user_name_modify, origin_value, update_value
                    FROM UUM.LZFY_UUM_USER_MODIFY_LOG 
                    WHERE input_date >= SYSDATE - INTERVAL ':Minutes' MINUTE
                    ORDER BY input_date DESC";

                var result = await connection.QueryAsync<UumUserModifyLog>(sql, new { Minutes = minutes });
                
                _logger.LogInformation("查询最近修改日志，时间范围: {Minutes}分钟, 记录数: {Count}", 
                    minutes, result.Count());
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询最近修改日志失败，时间范围: {Minutes}分钟", minutes);
                throw;
            }
        }
    }
}